Attribute VB_Name = "MUtilesBaseDatos"
Option Explicit

Public Function caseFuncSQL(campo As String, datos As Variant, valores As Variant, Optional nuevoCampo As String = "", Optional defaultValue As String = "")
    Dim vret As String
    Dim i As Integer
    Select Case (coneXion)
        Case "ACCESS":
            If Left(datos(i), 1) = "<" Or Left(datos(i), 1) = ">" Or UCase(Left(datos(i), 2)) = "IS" Then
                vret = vret & "iif(" & campo & datos(0) & "," & valores(0)
            Else
                vret = vret & "iif(" & campo & "=" & datos(0) & "," & valores(0)
            End If
            For i = 1 To UBound(valores)
                If Left(datos(i), 1) = "<" Or Left(datos(i), 1) = ">" Or UCase(Left(datos(i), 2)) = "IS" Then
                    vret = vret & ",iif(" & campo & datos(i) & "," & valores(i)
                Else
                    vret = vret & ",iif(" & campo & "=" & datos(i) & "," & valores(i)
                End If
            Next
            For i = 0 To UBound(valores)
                vret = vret & ")"
            Next
            If nuevoCampo = "" Then
                vret = vret & " as " & campo & "2"
            Else
                vret = vret & " as " & nuevoCampo
            End If
        Case "SQLSERVER":
            vret = vret & "case"
            For i = 0 To UBound(valores)
                If Left(datos(i), 1) = "<" Or Left(datos(i), 1) = ">" Or UCase(Left(datos(i), 2)) = "IS" Then
                    vret = vret & " when " & campo & datos(i) & " then " & valores(i)
                Else
                    vret = vret & " when " & campo & "=" & datos(i) & " then " & valores(i)
                End If
            Next
            If defaultValue <> "" Then
                vret = vret & " else " & defaultValue
            End If
            If nuevoCampo = "" Then
                vret = vret & " end as " & campo & "2"
            Else
                vret = vret & " end as " & nuevoCampo
            End If
    End Select
    caseFuncSQL = vret
End Function

Public Function caseFuncSQL2(condiciones As Variant, valores As Variant, nuevoCampo As String)
    Dim vret As String
    Dim i As Integer
    Select Case (coneXion)
        Case "ACCESS":
            vret = vret & "iif(" & condiciones(0) & "," & valores(0)
            For i = 1 To UBound(valores)
                vret = vret & ",iif(" & condiciones(i) & "," & valores(i)
            Next
            For i = 0 To UBound(valores)
                vret = vret & ")"
            Next
            vret = vret & " as " & nuevoCampo
        Case "SQLSERVER":
            vret = vret & "case"
            For i = 0 To UBound(valores)
                vret = vret & " when " & condiciones(i) & " then " & valores(i)
            Next
            vret = vret & " end as " & nuevoCampo
    End Select
    caseFuncSQL2 = vret
End Function

Public Function coalesceFuncSQL(campo As String, valorNull As String)
    Dim vret As String
    Select Case (coneXion)
        Case "ACCESS":
            vret = "iif(" & campo & " is null," & valorNull & "," & campo & ")"
        Case "SQLSERVER":
            vret = "coalesce(" & campo & "," & valorNull & ")"
    End Select
    coalesceFuncSQL = vret
End Function

Public Function castNumericFuncSQL(campo As String)
    Dim vret As String
    Select Case (coneXion)
        Case "ACCESS":
            vret = "Cdbl(" & campo & ")"
        Case "SQLSERVER":
            vret = "cast(" & campo & " as numeric(2))"
    End Select
    castNumericFuncSQL = vret
End Function

Public Function formatFechaSQL(campo_fecha As String) As String
'    Select Case (coneXion)
'        Case "ACCESS":
'            formatFechaSQL = "right(" & campo_fecha & ",2) + '/' + mid(" & campo_fecha & ",5,2) + '/' + left(" & campo_fecha & ",4)"
'        Case "SQLSERVER":
'            formatFechaSQL = "CONVERT(VARCHAR(10), " & campo_fecha & ", 103) as " & campo_fecha
'    End Select
    Select Case (coneXion)
        Case "ACCESS":
            formatFechaSQL = "right(" & campo_fecha & ",2) + '/' + mid(" & campo_fecha & ",5,2) + '/' + left(" & campo_fecha & ",4)"
        Case "SQLSERVER":
            formatFechaSQL = "dbo.cambiafecha(" & campo_fecha & ")"
    End Select
End Function

Public Function formatFuncSQL(campo_format As String, nrodecs As Integer) As String
    Select Case (coneXion)
        Case "ACCESS":
            Dim decs As String
            While nrodecs > 0
                decs = decs & "0"
                nrodecs = nrodecs - 1
            Wend
            If Strings.Len(nrodecs) > 0 Then
                formatFuncSQL = "format(" & campo_format & ",'0." & decs & "')"
            Else
                formatFuncSQL = "format(" & campo_format & ",'0')"
            End If
        Case "SQLSERVER":
            formatFuncSQL = "dbo.format(" & campo_format & "," & nrodecs & ")"
            'formatFuncSQL = "round(" & campo_format & "," & nrodecs & "," & nrodecs & ")"
            'formatFuncSQL = "round(" & campo_format & "," & nrodecs & ")"
    End Select
End Function

Public Function aumentaCerosFuncSQL(campoAumenta As String, nroCeros As Integer)
    Dim ceros As String
    Dim nroCerosTemp As Integer
    nroCerosTemp = nroCeros
    While nroCerosTemp > 0
        ceros = ceros & "0"
        nroCerosTemp = nroCerosTemp - 1
    Wend
    Select Case (coneXion)
        Case "ACCESS":
            If Strings.Len(nroCeros) > 0 Then
                aumentaCerosFuncSQL = "right('" & ceros & "' + trim(str(" & campoAumenta & "))," & nroCeros & ")"
            Else
                aumentaCerosFuncSQL = campoAumenta
            End If
        Case "SQLSERVER":
            aumentaCerosFuncSQL = "RIGHT('" & ceros & "'+ cast(" & campoAumenta & " as varchar(15))," & nroCeros & " )"
    End Select
End Function
'revisar con pruebas
Public Function FechaHoyServidor() As String
    Dim fRec As ClsRecordset
    Set fRec = New ClsRecordset
    Dim sql As String
    Select Case coneXion
        Case "ACCESS": sql = "SELECT date()"
        Case "SQLSERVER": sql = "SELECT getDate()"
    End Select
    fRec.AbreRec sql
    FechaHoyServidor = fRec.mrec.Fields(0)
End Function

Public Function MascaraSQL(ByVal numero As String)
    Dim temp As String
    If numero = "" Then
        numero = "0.00"
    End If
    temp = Strings.Format(numero, "########0.00")
    'MsgBox temp
    MascaraSQL = Strings.Replace(temp, gstrSepDecimal, ".")
End Function

Public Function BooleanToSiNoSql(nombreCampo As String, Optional mayusculas As Boolean = False)
    'funcion de rapido acceso para crear un sql que cambia True o False de BD por SI o NO
    If mayusculas Then
        BooleanToSiNoSql = caseFuncSQL2(Array(nombreCampo & "=" & formatBoolBD("1"), _
                                              nombreCampo & "=" & formatBoolBD("0")), _
                                        Array("'SI'", "'NO'"), nombreCampo)
    Else
        BooleanToSiNoSql = caseFuncSQL2(Array(nombreCampo & "=" & formatBoolBD("1"), _
                                              nombreCampo & "=" & formatBoolBD("0")), _
                                        Array("'Si'", "'No'"), nombreCampo)
    End If
End Function
